import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.Executors;

import javax.xml.parsers.DocumentBuilderFactory;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.methods.GetMethod;
import org.dom4j.Document;
import org.dom4j.Node;
import org.dom4j.io.DOMReader;

import edu.usc.cmb.iregulome.util.MLUtil;

public class ComplementaryStudyLibraryLayoutApp {

	public static void main(String[] args) throws IOException, SQLException,
			ClassNotFoundException {

		// Load the JDBC driver
		String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC
		// driver
		Class.forName(driverName);

		// Create a connection to the database
		String serverName = "zhoudb.usc.edu";
		String mydatabase = "seq_db";
		String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a
		// JDBC
		// url
		String username = "siyuanh";
		String password = "11";
		final Connection connection = DriverManager.getConnection(url,
				username, password);

		Statement stat = connection.createStatement();
		String sql = "select STUDY_ID FROM STUDY_TABLE;";

		final ResultSet rs = stat.executeQuery(sql);
		while (rs.next()) {
			final String id = rs.getString(1);

			Executors.newFixedThreadPool(50).submit(new R(connection, id));
		}

		// connection.close();

	}
}

class R implements Runnable {

	private final HttpClient client = new HttpClient();

	private final DOMReader reader = new DOMReader();

	private final static DocumentBuilderFactory dbf = DocumentBuilderFactory
			.newInstance();

	private final Connection conn;

	private final String id;

	public R(Connection conn, String id) {
		this.conn = conn;
		this.id = id;
	}

	@Override
	public void run() {
		try {

			Statement stat2 = conn.createStatement();

			System.out.println("======");

			System.out.println(id);

			String detailURLString = "http://www.ncbi.nlm.nih.gov/sites/entrez?db=sra&term="
					+ id + "&report=fullxml";

			String detailXml = "";
			// get detail xml
			{
				GetMethod getMethod = new GetMethod(detailURLString);
				client.executeMethod(getMethod);

				InputStream detailHtmlStream = getMethod
						.getResponseBodyAsStream();

				BufferedReader br = new BufferedReader(new InputStreamReader(
						detailHtmlStream));

				String lineHtml = br.readLine();
				while (lineHtml != null) {
					detailXml += lineHtml;
					lineHtml = br.readLine();
				}

				br.close();

				detailXml = detailXml.substring(detailXml
						.indexOf("EXPERIMENT_PACKAGE") - 4, detailXml
						.lastIndexOf("EXPERIMENT_PACKAGE")
						+ "EXPERIMENT_PACKAGE".length() + 4);
				detailXml = MLUtil.extractXMLFromHtml(detailXml);

				detailXml = detailXml.replaceAll("/<EXPERIMENT_PACKAGE>",
						"</EXPERIMENT_PACKAGE>");

			}

			// parse the xml

			Document doc = null;

			org.w3c.dom.Document root = dbf.newDocumentBuilder().parse(
					new ByteArrayInputStream(detailXml.getBytes()));

			doc = reader.read(root);
			int num = 0;
			Node theNode = doc
					.selectSingleNode("//EXPERIMENT_PACKAGE/EXPERIMENT/DESIGN/LIBRARY_DESCRIPTOR/LIBRARY_LAYOUT/SINGLE");
			if (theNode != null) {
				num = 1;
			}
			theNode = doc
					.selectSingleNode("//EXPERIMENT_PACKAGE/EXPERIMENT/DESIGN/LIBRARY_DESCRIPTOR/LIBRARY_LAYOUT/PAIRED");
			if (theNode != null) {
				num = 2;
			}
			if (num != 0) {
				String updateSql = "update STUDY_TABLE SET LIBRARY_LAYOUT = "
						+ num + " WHERE STUDY_ID = '" + id + "'";

				stat2.executeUpdate(updateSql);

			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}